import json
from typing import Union
from math import ceil
from typing import Dict, List, Any, Generator

import orjson
import sys
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, or_, and_
from sqlalchemy.ext.declarative import as_declarative, declared_attr
from sqlalchemy.orm import sessionmaker, Session, Query
from sqlalchemy.engine.row import Row

from setting import config
from common.utils import JsonDataEncoder, is_empty
from core.logger import logger

engine = create_engine(
    config.SQLALCHEMY_DATABASE_URI,
    pool_size=config.POOL_SIZE,
    # pool_timeout=config.POOL_TIMEOUT,
    # pool_recycle=config.POOL_RECYCLE,
    # pool_pre_ping=config.POOL_PRE_PING
)


# https://www.manongdao.com/article-1376438.html
class CustomQuery(Query):

    @staticmethod
    def anytype_to_str(row: Dict) -> Dict:
        if isinstance(row, Row):
            item = json.dumps(dict(zip(row.keys(), row)), cls=JsonDataEncoder)
        else:
            item = json.dumps({c.name: getattr(row, c.name, None) for c in row.__table__.columns},
                              cls=JsonDataEncoder)
        return orjson.loads(item)

    def to_dict(self) -> List[Dict]:
        rows = self.all()
        if not rows:
            return []
        items = []
        for item in rows:
            items.append(CustomQuery.anytype_to_str(item))
        return items

    def to_dict_first(self) -> Dict:
        row = self.first()
        if not row:
            return {}
        item = CustomQuery.anytype_to_str(row)
        return item

    # https://www.cnblogs.com/rgcLOVEyaya/p/RGC_LOVE_YAYA_350days.html
    def paginate(self, page: int = 1, per_page: int = 10):
        if page < 1:
            return {}

        if per_page < 0:
            return {}

        items = self.limit(per_page).offset((page - 1) * per_page).to_dict()
        total = self.order_by(None).count()
        total_page = int(ceil(total / float(per_page)))

        return {"current_page": page, "per_page": per_page, "items": items,
                "total_page": total_page, "total": total}


SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine, query_cls=CustomQuery)


@as_declarative()
class Base:
    # 通用字段
    uuid = Column(Integer, primary_key=True, autoincrement=True, comment='uuid')

    @declared_attr
    def __tablename__(cls) -> str:
        import re
        # 如果没有指定__tablename__  则默认使用model类名转换表名字
        name_list = re.findall(r"[A-Z][a-z\d]*", cls.__name__)
        # 表明格式替换成_格式 如 AuthUser 替换成 auth_user
        return "_".join(name_list).lower()


async def get_db() -> Generator:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


class SessionMixin(object):

    # @classmethod
    # def add(cls, db: session, data) -> None:
    #     obj = cls(**data)
    #     obj.save(db)
    #
    # def save(self, db: session):
    #     """
    #     save model
    #     """
    #     db.add(self)
    #     db.commit()
    #     return self

    @classmethod
    def update(cls, db: Session, data):
        try:
            result = db.query(cls).filter_by(uuid=data["uuid"]).update(data)
            db.commit()
        except Exception as e:
            db.rollback()
            logger.error(f'update error => {e}')
            return None
        return result

    @classmethod
    def add(cls, db: Session, data):
        try:
            obj = cls(**data)
            db.add(obj)
            db.commit()
            db.refresh(obj)
        except Exception as e:
            db.rollback()
            logger.error(f'add error => {e}')
            return None

        return obj

    @classmethod
    def save(cls, db: Session, data: Dict) -> None:
        """
        save model
        """
        db.bulk_insert_mappings(cls, [data])
        db.commit()

    @classmethod
    def saves(cls, db: Session, data: List[Dict]) -> None:
        """
        batch save model
        """
        db.bulk_insert_mappings(cls, data)
        db.commit()

    @classmethod
    def get_object_by_name(cls, db: Session, name: str):
        object = db.query(cls).filter(
            cls.name == name
        ).first()
        return object

    @classmethod
    def get_object_info_by_name(cls, db: Session, name: str):
        object = cls.get_object_by_name(db, name)
        return object.object_to_dict(db)

    @classmethod
    def delete_object_by_name(cls, db: Session, name: str):
        object = cls.get_object_by_name(db, name)
        object.delete(db)

    @classmethod
    def get_object_by_uuid(cls, db: Session, uuid: int):
        object = db.query(cls).filter(
            cls.uuid == uuid
        ).first()
        return object

    @classmethod
    def get_object_info_by_uuid(cls, db: Session, uuid: int):
        object = cls.get_object_by_uuid(db, uuid)
        return object.object_to_dict(db)

    @classmethod
    def delete_object_by_uuid(cls, db: Session, uuid: int):
        object = cls.get_object_by_uuid(db, uuid)
        object.delete(db)

    def delete(self, db):
        """
        delete model
        """
        try:
            db.delete(self)
            db.commit()
        except Exception as e:
            db.rollback()
            logger.error(f'delete error => {e}')
            return False
        return True

    @classmethod
    def get_entries(cls, db: Session) -> List[Dict]:
        return db.query(cls).to_dict()

    @classmethod
    def get_display_field_meta(cls):
        """
        每个字段的说明
        :return:
        """
        data = []
        try:
            for key, col in cls.__table__.c.items():
                if not col.comment.startswith("_"):
                    data.append({
                        'field': key,
                        'comment': col.comment
                    })
        except Exception as e:
            logger.error(f'{sys._getframe().f_code.co_name} {e}')

        return data

    @classmethod
    def get_display_field(cls):
        """
        查询需要显示的字段
        :return:
        """
        data = []
        try:
            for key, col in cls.__table__.c.items():
                comment = col.comment.split(' ')[0]
                if not comment.startswith("_"):
                    data.append(col)
        except Exception as e:
            logger.error(f'{sys._getframe().f_code.co_name} {e}')

        return data

    # 根据字段的值模糊匹配，或者全表模糊查询
    @classmethod
    def filter_by_field_search(cls, db: Session, page: int, per_page: int, field_info: dict, order_info: dict):
        try:
            # 获取要显示的字段
            display_field = cls.get_display_field()

            # 字段的过滤条件
            search_field = []
            for field, value in field_info.items():
                if field == "all" and not value:
                    continue
                for key, col in cls.__table__.c.items():
                    if field == "all" or key == field:
                        search_field.append(col.like(f"%{value}%"))

            # 字段的排序
            order_field = []
            for field, value in order_info.items():
                for key, col in cls.__table__.c.items():
                    if key == field and value == "asc":
                        order_field.append(col.asc())
                    if key == field and value == "desc":
                        order_field.append(col.desc())

            result = db.query(cls).filter(or_(
                *search_field
            )).with_entities(
                *display_field
            ).order_by(*order_field).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.error(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 添加格外的字段
        result["items"] = [
            cls.get_object_info_by_uuid(db, item["uuid"])
            for item in result["items"]
        ]
        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result

    # 根据字段的值完全匹配查询
    @classmethod
    def filter_by_field_match(cls, db: Session, page: int, per_page: int, field_info: dict):
        try:
            search_field = []
            display_field = cls.get_display_field()
            for field, value in field_info.items():
                for key, col in cls.__table__.c.items():
                    if key == field:
                        search_field.append(col == value)
            if len(search_field) == 0:
                logger.error(f'field:{field_info.keys()} not in table')
                return None

            result = db.query(cls).filter(
                and_(*search_field)
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.error(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 添加格外的字段
        result["items"] = [
            cls.get_object_info_by_uuid(db, item["uuid"])
            for item in result["items"]
        ]
        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result

    @classmethod
    def to_dict(cls, row: Any) -> Dict:
        if isinstance(row, Row):
            res = json.dumps(dict(zip(row.keys(), row)), cls=JsonDataEncoder)
        else:
            res = json.dumps({c.name: getattr(row, c.name, None) for c in row.__table__.columns}, cls=JsonDataEncoder)
        return orjson.loads(res)

    @classmethod
    def to_list(cls, row: Any) -> List:
        if isinstance(row, Row):
            res = json.dumps(list(row), cls=JsonDataEncoder)
        else:
            res = json.dumps([getattr(row, c.name, None) for c in row.__table__.columns], cls=JsonDataEncoder)
        return orjson.loads(res)
